﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using Entities;

namespace DataAccess
{
	public class ListDAL
	{
		private string connectionString = "Data Source=" + new System.IO.DirectoryInfo(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\\DoubleFeature").FullName + "\\Resources\\movies.sqlite";

		public bool Save(MovieList entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("INSERT INTO lists (list_name) VALUES (?)", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Name;

				cmd.Parameters.Add(param1);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool Update(MovieList entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("UPDATE lists SET list_name=? WHERE list_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Name;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.Id;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool AddMovie(MovieList entity, Movie movie)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("INSERT INTO relationships (movie_id, relationship_element, relationship_type) VALUES (?, ?, 'list')", conn);

				SQLiteParameter paramMovieIndex = new SQLiteParameter();
				paramMovieIndex.Value = movie.Id;
				SQLiteParameter paramListIndex = new SQLiteParameter();
				paramListIndex.Value = entity.Id;

				cmd.Parameters.Add(paramMovieIndex);
				cmd.Parameters.Add(paramListIndex);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool DeleteMovie(MovieList entity, Movie movie)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("DELETE FROM relationships WHERE movie_id=? AND relationship_element=? AND relationship_type='list'", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = movie.Id;
				SQLiteParameter param2 = new SQLiteParameter();
				param2.Value = entity.Id;

				cmd.Parameters.Add(param1);
				cmd.Parameters.Add(param2);

				conn.Open();

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public bool Delete(MovieList entity)
		{
			try
			{
				SQLiteConnection conn = new SQLiteConnection(connectionString);

				SQLiteCommand cmd = new SQLiteCommand("DELETE FROM lists WHERE list_id=?", conn);

				SQLiteParameter param1 = new SQLiteParameter();
				param1.Value = entity.Id;

				cmd.Parameters.Add(param1);

				conn.Open();

				cmd.ExecuteNonQuery();

				cmd.CommandText = "DELETE FROM relationships WHERE relationship_element=" + entity.Id + " AND relationship_type='list'";

				cmd.ExecuteNonQuery();

				conn.Close();

				return true;
			}
			catch (Exception e)
			{
				//log
				return false;
			}
		}

		public List<MovieList> List()
		{
			SQLiteConnection conn = new SQLiteConnection(connectionString);
			SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM lists ORDER BY list_id ASC", conn);

			conn.Open();

			List<MovieList> collection = new List<MovieList>();

			SQLiteDataReader dr = cmd.ExecuteReader();

			while (dr.Read())
			{
				MovieList obj = new MovieList();

				obj.Id = Convert.ToInt32(dr["list_id"]);
				obj.Name = dr["list_name"].ToString();

				collection.Add(obj);
			}

			conn.Close();

			return collection;
		}
	}
}
